-- select *from fn_load_all_transit_lots(0) where PROCESS_TYPE_NAME ='FAB' and LOT_NUMBER = 'Q92586.1'
 
ALTER FUNCTION FN_LOAD_ALL_TRANSIT_LOTS( @PROCESS_STEP_IDS INT )

RETURNS  @FN_RESULT TABLE( PROCESSSTEP_ID INT, LOT_NUMBER VARCHAR(50),FAB_LOT_NUMBER VARCHAR(50),PART_NUMBER VARCHAR(50),PO_NUMBER VARCHAR(50),SO_NUMBER VARCHAR(50),INVOICE VARCHAR(50),PS_TYPE VARCHAR(50),
				PREVIOUS_LOC VARCHAR(50),NEXT_LOC VARCHAR(50),SHIP_DATE DATETIME, SHIP_QTY INT, GDPW INT, GROSS_DIE INT, PS_TYPE_ID INT, SHIPPEDLOT_ID INT,WAFER_ID VARCHAR(100),SHIP_REQ_ID INT, LOT_ID BIGINT,STD_COST NUMERIC(18,4) )
AS
BEGIN


	DECLARE @LOT_NUMBER 	VARCHAR(50)
	DECLARE @FAB_LOT_NUMBER VARCHAR(50)
	DECLARE @PART_NUMBER 	VARCHAR(50)
	DECLARE @PO_NUMBER 	VARCHAR(50)
	DECLARE @SO_NUMBER 	VARCHAR(50)
	DECLARE @INVOICE 	VARCHAR(50)
	DECLARE @PREVIOUS_LOC 	VARCHAR(50)
	DECLARE @NEXT_LOC 	VARCHAR(50)
	DECLARE @WAFER_ID	VARCHAR(100)
	DECLARE @SHIP_DATE 	DATETIME
	DECLARE @PS_TYPE    VARCHAR(50) 
	DECLARE @SHIP_QTY 	INT
	DECLARE @PS_TYPE_ID	INT 
	DECLARE @SHIP_REQ_ID	INT
	DECLARE @LOT_ID		BIGINT
	DECLARE @PROCESS_STEPS_TABLE TABLE( PROCESSSTEP_ID INT )
	DECLARE @PROCESSTEP_ID INT
	INSERT INTO @PROCESS_STEPS_TABLE( PROCESSSTEP_ID )  SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @PROCESS_STEP_IDS )

	DECLARE @STD_COST NUMERIC(18,4)
	DECLARE @IS_HOLD BIT
	DECLARE @GORSS_DIE_PER_WAFER INT
	DECLARE @GORSS_DIE INT
	DECLARE @SHIPPEDLOT_ID INT
			
	DECLARE @RESULTS TABLE ( PROCESSSTEP_ID INT, LOT_NUMBER VARCHAR(50),FAB_LOT_NUMBER VARCHAR(50),PART_NUMBER VARCHAR(50),PO_NUMBER VARCHAR(50),SO_NUMBER VARCHAR(50),INVOICE VARCHAR(50),PS_TYPE VARCHAR(50),
				PREVIOUS_LOC VARCHAR(50),NEXT_LOC VARCHAR(50),SHIP_DATE DATETIME, SHIP_QTY INT, GDPW INT, GROSS_DIE INT, PS_TYPE_ID INT, SHIPPEDLOT_ID INT,WAFER_ID VARCHAR(100), SHIP_REQ_ID INT, LOT_ID BIGINT,STD_COST NUMERIC(18,4) )

	DECLARE @TRANSITCURSOR CURSOR
	

	IF ( @PROCESS_STEP_IDS <> '0')
	BEGIN
		
		SET @TRANSITCURSOR = CURSOR FOR SELECT PS.PROCESSSTEP_ID, LT.LOT_NUMBER, LT.FAB_LOT_NUMBER, P.PART_NUMBER , ISNULL(SL.SL_Customer_PO,LT.PO_NUMBER) , SL.SL_CUSTOMER_SO,
		SL.SL_INVOICE_NUMBER , PS.PS_TYPE, (SELECT L_VENDOR_NAME FROM lOCATION where location_id = LT.Location_ID ) , SL.SL_NEXTLOCATION, SL.SHIP_DATE, SL.SHIP_QTY, SL.SHIPPEDLOT_ID,
		SL.WAFER_ID,SL.SHIP_REQ_ID,SL.LOT_ID,(SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = PS.PS_Type)
		FROM LOT_TABLE LT, SHIPPED_LOT SL, PRODUCT P, PROCESS_STEP PS
		WHERE SL.LOT_ID = LT.LOT_ID AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
		AND P.PROCESSSTEP_ID IN( SELECT PROCESSSTEP_ID FROM @PROCESS_STEPS_TABLE  ) AND SL.STATUS = 'TRANSIT' AND SL.SHIP_STATUS <= 0
		
	END
	ELSE
	BEGIN
			
		SET @TRANSITCURSOR = CURSOR FOR SELECT PS.PROCESSSTEP_ID, LT.LOT_NUMBER, LT.FAB_LOT_NUMBER, P.PART_NUMBER , ISNULL(SL.SL_Customer_PO,LT.PO_NUMBER) , SL.SL_CUSTOMER_SO,
			SL.SL_INVOICE_NUMBER , PS.PS_TYPE, (SELECT L_VENDOR_NAME FROM lOCATION where location_id = LT.Location_ID ) , SL.SL_NEXTLOCATION, SL.SHIP_DATE, SL.SHIP_QTY, SL.SHIPPEDLOT_ID,
			SL.WAFER_ID,SL.SHIP_REQ_ID,SL.LOT_ID,(SELECT SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = PS.PS_Type)
		FROM LOT_TABLE LT, SHIPPED_LOT SL, PRODUCT P, PROCESS_STEP PS
		WHERE SL.LOT_ID = LT.LOT_ID AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
		AND SL.STATUS = 'TRANSIT' AND SL.SHIP_STATUS <= 0
	END

	OPEN @TRANSITCURSOR
	FETCH NEXT FROM @TRANSITCURSOR INTO @PROCESSTEP_ID, @LOT_NUMBER, @FAB_LOT_NUMBER, @PART_NUMBER , @PO_NUMBER , @SO_NUMBER,  @INVOICE , @PS_TYPE, @PREVIOUS_LOC , @NEXT_LOC , @SHIP_DATE, @SHIP_QTY, @SHIPPEDLOT_ID ,@WAFER_ID,@SHIP_REQ_ID,@LOT_ID,@PS_TYPE_ID  

	WHILE @@FETCH_STATUS = 0
	BEGIN
		
		IF @PS_TYPE = 'FAB'
		BEGIN
			SELECT @GORSS_DIE_PER_WAFER = Parameter_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @PROCESSTEP_ID AND Parameter_Key = 'Gross Die/Wafer'
			--SELECT @STD_COST = Parameter_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @PROCESSTEP_ID AND Parameter_Key = 'Standard Unit Cost'
			SET @GORSS_DIE				= @SHIP_QTY * @GORSS_DIE_PER_WAFER
		END

		INSERT INTO @RESULTS(PROCESSSTEP_ID, LOT_NUMBER, FAB_LOT_NUMBER, PART_NUMBER,PO_NUMBER,SO_NUMBER,INVOICE,PS_TYPE,PREVIOUS_LOC,NEXT_LOC,SHIP_DATE,SHIP_QTY, GDPW, GROSS_DIE, PS_TYPE_ID, SHIPPEDLOT_ID,WAFER_ID,SHIP_REQ_ID, LOT_ID) 
		VALUES (@PROCESSTEP_ID, @LOT_NUMBER, @FAB_LOT_NUMBER, @PART_NUMBER,@PO_NUMBER,@SO_NUMBER,@INVOICE,@PS_TYPE,@PREVIOUS_LOC,@NEXT_LOC,@SHIP_DATE,@SHIP_QTY, @GORSS_DIE_PER_WAFER, @GORSS_DIE, @PS_TYPE_ID, @SHIPPEDLOT_ID,@WAFER_ID,@SHIP_REQ_ID, @LOT_ID) 
	
		SET @GORSS_DIE_PER_WAFER = NULL
		SET @GORSS_DIE = NULL
		SET @STD_COST = NULL
		FETCH NEXT FROM @TRANSITCURSOR INTO @PROCESSTEP_ID, @LOT_NUMBER, @FAB_LOT_NUMBER,@PART_NUMBER , @PO_NUMBER , @SO_NUMBER, @INVOICE , @PS_TYPE, @PREVIOUS_LOC , @NEXT_LOC , @SHIP_DATE, @SHIP_QTY, @SHIPPEDLOT_ID,@WAFER_ID,@SHIP_REQ_ID,@LOT_ID,@PS_TYPE_ID
		
	END
	CLOSE @TRANSITCURSOR
	DEALLOCATE @TRANSITCURSOR
	
	INSERT @FN_RESULT SELECT * FROM @RESULTS ORDER BY PS_TYPE_ID, SHIP_DATE,PART_NUMBER DESC  		
	
RETURN
END

